"""add_crl_and_ocsp_tables

Revision ID: 41b8ee38619d
Revises: 63766cfa6a42
Create Date: 2025-06-18 23:11:51.077057

"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
import sqlmodel


# revision identifiers, used by Alembic.
revision: str = "41b8ee38619d"
down_revision: Union[str, None] = "63766cfa6a42"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "certificate_revocation_lists",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("version", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("crl_number", sa.Integer(), nullable=False),
        sa.Column("issuer", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("this_update", sa.DateTime(), nullable=False),
        sa.Column("next_update", sa.DateTime(), nullable=False),
        sa.Column(
            "status",
            sa.Enum("CURRENT", "SUPERSEDED", "EXPIRED", name="crlstatus"),
            nullable=False,
        ),
        sa.Column("revoked_certificates_count", sa.Integer(), nullable=False),
        sa.Column("crl_der", sa.LargeBinary(), nullable=False),
        sa.Column("crl_pem", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("crl_size", sa.Integer(), nullable=False),
        sa.Column("distribution_points", sa.JSON(), nullable=True),
        sa.Column(
            "signature_algorithm", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column(
            "signature_key_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_certificate_revocation_lists_version"),
        "certificate_revocation_lists",
        ["version"],
        unique=True,
    )
    op.create_table(
        "ocsp_requests",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("request_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column(
            "certificate_serial", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column(
            "issuer_key_hash", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column(
            "issuer_name_hash", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column("hash_algorithm", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("client_ip", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("user_agent", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("request_der", sa.LargeBinary(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_ocsp_requests_certificate_serial"),
        "ocsp_requests",
        ["certificate_serial"],
        unique=False,
    )
    op.create_index(
        op.f("ix_ocsp_requests_request_id"),
        "ocsp_requests",
        ["request_id"],
        unique=True,
    )
    op.create_table(
        "ocsp_responders",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("name", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column(
            "certificate_pem", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column(
            "private_key_pem", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column(
            "certificate_serial", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("endpoints", sa.JSON(), nullable=True),
        sa.Column("max_request_size", sa.Integer(), nullable=False),
        sa.Column("response_timeout_seconds", sa.Integer(), nullable=False),
        sa.Column("supported_extensions", sa.JSON(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("name"),
    )
    op.create_table(
        "ocsp_responses",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("request_id", sa.Uuid(), nullable=False),
        sa.Column(
            "certificate_serial", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column(
            "cert_status",
            sa.Enum("GOOD", "REVOKED", "UNKNOWN", name="ocspresponsestatus"),
            nullable=False,
        ),
        sa.Column("this_update", sa.DateTime(), nullable=False),
        sa.Column("next_update", sa.DateTime(), nullable=True),
        sa.Column("revocation_time", sa.DateTime(), nullable=True),
        sa.Column(
            "revocation_reason",
            sa.Enum(
                "UNSPECIFIED",
                "KEY_COMPROMISE",
                "CA_COMPROMISE",
                "AFFILIATION_CHANGED",
                "SUPERSEDED",
                "CESSATION_OF_OPERATION",
                name="revocationreason",
            ),
            nullable=True,
        ),
        sa.Column("responder_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column(
            "responder_key_hash", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column("response_der", sa.LargeBinary(), nullable=False),
        sa.Column("response_size", sa.Integer(), nullable=False),
        sa.Column(
            "signature_algorithm", sqlmodel.sql.sqltypes.AutoString(), nullable=False
        ),
        sa.Column("processing_time_ms", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(
            ["request_id"],
            ["ocsp_requests.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_ocsp_responses_certificate_serial"),
        "ocsp_responses",
        ["certificate_serial"],
        unique=False,
    )
    op.create_table(
        "revoked_certificate_entries",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("crl_id", sa.Uuid(), nullable=False),
        sa.Column("serial_number", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("revocation_date", sa.DateTime(), nullable=False),
        sa.Column(
            "revocation_reason",
            sa.Enum(
                "UNSPECIFIED",
                "KEY_COMPROMISE",
                "CA_COMPROMISE",
                "AFFILIATION_CHANGED",
                "SUPERSEDED",
                "CESSATION_OF_OPERATION",
                name="revocationreason",
            ),
            nullable=False,
        ),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(
            ["crl_id"],
            ["certificate_revocation_lists.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_revoked_certificate_entries_serial_number"),
        "revoked_certificate_entries",
        ["serial_number"],
        unique=False,
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(
        op.f("ix_revoked_certificate_entries_serial_number"),
        table_name="revoked_certificate_entries",
    )
    op.drop_table("revoked_certificate_entries")
    op.drop_index(
        op.f("ix_ocsp_responses_certificate_serial"), table_name="ocsp_responses"
    )
    op.drop_table("ocsp_responses")
    op.drop_table("ocsp_responders")
    op.drop_index(op.f("ix_ocsp_requests_request_id"), table_name="ocsp_requests")
    op.drop_index(
        op.f("ix_ocsp_requests_certificate_serial"), table_name="ocsp_requests"
    )
    op.drop_table("ocsp_requests")
    op.drop_index(
        op.f("ix_certificate_revocation_lists_version"),
        table_name="certificate_revocation_lists",
    )
    op.drop_table("certificate_revocation_lists")
    # ### end Alembic commands ###
